package com.zhiyou100.dao.Impl;

import com.zhiyou100.JDBCUtil.DBUtil;
import com.zhiyou100.dao.HouseDao;
import com.zhiyou100.emtity.House;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * @description:
 * @author: 马跃
 * @create: 2021-01-22 14:09
 **/
public class HouseDaoImpl implements HouseDao {

    private QueryRunner qur = new QueryRunner(DBUtil.getDataSource());

    @Override
    public List<com.zhiyou100.emtity.House> queryAll() {
        String sql = "select * from house";
        try {
            List<House> query = qur.query(sql, new BeanListHandler<>(House.class));
            return query;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public void delete(int id) {
        String sql = "delete from house where h_id = ?";
        try {
            qur.update(sql,id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return;
    }

    @Override
    public void add(House house) {
        String sql = "insert into house values(null,?,null ,?,?,?,?,?,?,?,?,?,?,?,?,null ,?,null )";
        try {
            qur.update(sql, house.getH_area(),
                    house.getH_unitNumber(),
                    house.getH_floor(),
                    house.getH_roomNumber(),
                    house.getH_acreage(),
                    house.getH_direction(),
                    house.getH_fitment(),
                    house.getH_isDoubleAir(),
                    house.getH_limit(),
                    house.getH_facility(),
                    house.getH_img(),
                    house.getH_address(),
                    house.getH_price(),
                    new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return;
    }

    @Override
    public House query(int id) {
        String sql = "SELECT h.*,l.l_name,l.l_phone,c.c_endTime\n" +
                "FROM house h\n" +
                "JOIN rent r\n" +
                "ON h.h_id=r.h_id\n" +
                "JOIN lessee l\n" +
                "ON r.l_id=l.l_id\n" +
                "JOIN contract c\n" +
                "on c.h_id=h.h_id\n" +
                "WHERE h.h_id=?\n" +
                "GROUP BY 1";
        try {
            House query = qur.query(sql, new BeanHandler<>(House.class), id);
            return query;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public void update(House house) {
        String sql="update house set" +
                "h_estate=?," +
                "h_unitNumber?" +
                "h_floor=?" +
                "h_roomNumber=?" +
                "h_acreage=?" +
                "h_direction=?" +
                "h_fitment=?" +
                "h_limit=?" +
                "h_isDoubleAir=?" +
                "h_facility=?" +
                "h_address=?" +
                "h_img=?" +
                "h_price=? where h_id=?";
        try {
            qur.update(sql,
                    house.getH_estate(),
                    house.getH_unitNumber(),
                    house.getH_floor(),
                    house.getH_roomNumber(),
                    house.getH_acreage(),
                    house.getH_direction(),
                    house.getH_fitment(),
                    house.getH_limit(),
                    house.getH_isDoubleAir(),
                    house.getH_facility(),
                    house.getH_address(),
                    house.getH_img(),
                    house.getH_price(),
                    house.getH_id());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return;
    }
}
